package com.dmp.report

import com.dmp.config.ConfigHandler
import org.apache.spark.sql.{SQLContext, SaveMode}
import org.apache.spark.{SparkConf, SparkContext}

object AreaAnalysisSQL {
  def main(args: Array[String]): Unit = {

    val sparkConf = new SparkConf()
    sparkConf.setAppName("地域分布统计-core")
    sparkConf.setMaster("local[*]")
    // 设置spark程序采用的序列化方式
    sparkConf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
    val sc = new SparkContext(sparkConf)
    val sQLContext = new SQLContext(sc)

    // 读取数据
    val dataFrame = sQLContext.read.parquet(ConfigHandler.parquetFilePath)
    //sql -> 表
    dataFrame.registerTempTable("logs")

    /*sQLContext.sql(
        """
          |
          |select provincename, cityname,
          |sum(case when requestmode=1 and processnode>=1 then 1 else 0 end) adRawReq,
          |sum(case when requestmode=1 and processnode>=2 then 1 else 0 end) adEffReq,
          |sum(case when requestmode=1 and processnode=3 then 1 else 0 end) adReq,
          |sum(case when iseffective=1 and isbilling=1 and isbid=1 and adorderid!=0 then 1 else 0 end) adRtbReq,
          |sum(case when iseffective=1 and isbilling=1 and iswin=1 then 1 else 0 end) adSuccReq,
          |sum(case when requestmode=2 and iseffective=1 then 1 else 0 end) adShow,
          |sum(case when requestmode=3 and iseffective=1 then 1 else 0 end) adClick,
          |sum(case when iseffective=1 and isbilling=1 and iswin=1 then winprice/1000 else 0 end) adCost,
          |sum(case when iseffective=1 and isbilling=1 and iswin=1 then adpayment/1000 else 0 end) adConsumption
          |
          |from logs
          |group by provincename, cityname
        """.stripMargin).show()*/


    sQLContext.sql(
      """
        |select provincename, cityname,
        |sum(if(requestmode=1 and processnode>=1, 1, 0)) adRawReq,
        |sum(if(requestmode=1 and processnode>=2, 1, 0)) adEffReq,
        |sum(case when requestmode=1 and processnode=3 then 1 else 0 end) adReq,
        |sum(case when iseffective=1 and isbilling=1 and isbid=1 and adorderid!=0 then 1 else 0 end) adRtbReq,
        |sum(case when iseffective=1 and isbilling=1 and iswin=1 then 1 else 0 end) adSuccReq,
        |sum(case when requestmode=2 and iseffective=1 then 1 else 0 end) adShow,
        |sum(case when requestmode=3 and iseffective=1 then 1 else 0 end) adClick,
        |sum(case when iseffective=1 and isbilling=1 and iswin=1 then winprice/1000 else 0 end) adCost,
        |sum(case when iseffective=1 and isbilling=1 and iswin=1 then adpayment/1000 else 0 end) adConsumption
        |
        |from logs
        |group by provincename, cityname
      """.stripMargin)
      .write.mode(SaveMode.Overwrite).jdbc(ConfigHandler.url, "orc_report_area1", ConfigHandler.prop)


    sc.stop()
  }
}
